import pandas as pd
import pymysql
import os


def save_db(db, stockcode, path):

    cursor = db.cursor()

    create = 'CREATE TABLE IF NOT EXISTS `stock-{}`' \
        '(time DATE, price FLOAT, percentage FLOAT,' \
        'zl FLOAT, `zl(%%)` FLOAT,' \
        'cdd FLOAT, `cdd(%%)` FLOAT,' \
        'dd FLOAT, `dd(%%)` FLOAT,' \
        'zd FLOAT, `zd(%%)` FLOAT,' \
        'xd FLOAT, `xd(%%)` FLOAT)'.format(stockcode)
    cursor.execute(create) # 如果不存在数据表则创建
    db.commit()

    latest_date = 'select time from `stock-{}` order by time desc limit 1'.format(stockcode)
    cursor.execute(latest_date)
    res = cursor.fetchone() # 获取数据表中已有数据的最新日期

    data = pd.read_csv(path)
    if res != None: # 如果数据表为空，则跳过该步
        end = data[data['date'] == str(res[0])].index.values[0]
        data.drop(data.index[0:end+1], inplace=True) # 根据已有的日期，删除不需要插入的重复数据
    
    data_list = data.values.tolist()

    if data_list == []:
        cursor.close()
        return # 如果需要更新的数据为空，则直接返回
    
    data_tuple = [tuple(l) for l in data_list] # 转换每行要插入的数据的类型为tuple

    argss = ','.join(['%s']*len(data_list[0])) # 要插入的值对应的参数列表

    insert = 'insert into `stock-{}` values ({})'.format(stockcode, argss) # 构造动态SQL语句
    try:
        cursor.executemany(insert, data_tuple)
        db.commit()
    except:
        db.rollback() # 失败则回滚

    cursor.close()


def main():
    db = pymysql.connect(host='localhost', user='root',
                         password='257911', port=3306, db='stock')
    allcsv = os.listdir('./data')

    #for i, csvfile in enumerate(['000001.csv']):
    for i, csvfile in enumerate(allcsv):

        save_db(db, csvfile[0:6], './data/%s' % csvfile)

        if i % 10 == 0:
            print(str(round(i / len(allcsv) * 100, 2)) + '%' + ' is completed')

    print('OK!')
    db.close()


if __name__ == '__main__':
    main()
